﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net.Mail;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using Ceva.Data;
using Ceva.Net;
using Ceva.Utility;

namespace WinService
{
    public class MailReminderUser
    {
        #region Private Memebers
        private readonly string constr = ConfigurationManager.AppSettings["ConStr"].ToString();
        private const string headerStyle = "font-size: 7.5pt;height: 19px;font-weight: bold;text-align: center;font-family: Tahoma, Verdana, Arial, Helvetica, MS Sans Serif;font-size: 7.5pt;background-color:#606060;color:#ffffff";
        private const string mailBodyStyle = "font-family: Tahoma, Verdana, Arial, Helvetica, MS Sans Serif;font-size: 7.5pt;color: #000000;";
        private const string rowStyle = "font-family: Tahoma, Verdana, Arial, Helvetica, MS Sans Serif;font-size: 7.5pt;background-color:{0};";
        //padding:0in 5.4pt 0in 5.4pt

        private const string tdHeaderStyleLeft = "border:solid white 1.0pt;width:25px;background-color:#606060;color:#ffffff;padding:0in 5.4pt 0in 5.4pt;";
        private const string tdHeaderStyleOther = "border:solid white 1.0pt;height:15.0pt;border-left:none white 0in;padding:0in 5.4pt 0in 5.4pt;background-color:#606060;color:#ffffff";
        private const string tdbodyStyleLeft = "width:25px;border-top:none;border-left:solid white 1.0pt;border-bottom:solid white 1.0pt;border-right:solid white 1.0pt;height:15.0pt";
        private const string tdbodyStyleOther = "border-top:none;border-left:none;border-bottom:solid white 1.0pt;border-right:solid white 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt";
        private static readonly string aneeta = "";
        //private const string tdHeaderStyleLeft = "border:solid white 1.0pt;height:15.0pt;width:25px";
        //private const string tdHeaderStyleOther = "border:solid white 1.0pt;height:15.0pt;";
        //private const string tdbodyStyleLeft = "width:25px;border:solid white 1.0pt;height:15.0pt";
        //private const string tdbodyStyleOther = "border:solid white 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt";


        #endregion

        #region User Details
        public void MailReminderToUser()
        {
            Logger.HandleException("Starting Notification Process");
            try
            {
                DataSet ds = GetUserReminderData();
                List<UserInfo> lstUser = new List<UserInfo>();
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    Logger.HandleException("Total users for pending approval " + ds.Tables[0].Rows.Count.ToString());
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        UserInfo objUser = new UserInfo();
                        objUser = GetUserData(dr["UserId"].ToString());
                        objUser.AddedDate = Convert.ToDateTime(dr["AddedDate"]);
                        objUser.NoOfPendingDays = Convert.ToInt32(dr["NoOfPendingDays"]);
                        objUser.ApproverEmailId = GetApproverMailId(objUser, dr["ActionValue"].ToString(), dr["NextActionEmail"].ToString(), dr["NextActionId"].ToString());
                        lstUser.Add(objUser);
                    }
                    ProcessUserMail(lstUser);
                }
                else
                {
                    Logger.HandleException("No Data found for notification");
                }

            }
            catch (Exception ex)
            {
                Logger.HandleException(ex);
            }

        }

        private void ProcessUserMail(List<UserInfo> lstUser)
        {
            bool isSendMail = false;
            StringBuilder tmpsbMailBody = new StringBuilder();
            StringBuilder CCAddressList = new StringBuilder();
            string tableHeader = GetTableHeader();
            int i = 1;
            List<UserInfo> sortedList = lstUser.OrderBy(o => o.ApproverEmailId).ToList();
            if (sortedList.Count == 1)
            {
                CCAddressList.Append(sortedList[0].EmailAddress);
                GetUserApprovalMailBody(sortedList[0], ref tmpsbMailBody, i);
                SendUserMail(tmpsbMailBody, sortedList[0].ApproverEmailId, tableHeader, CCAddressList.ToString());
            }
            else
            {
                for (int iCount = 0; iCount < sortedList.Count; iCount++)
                {
                    if (iCount == 0 && sortedList[iCount].ApproverEmailId != sortedList[iCount + 1].ApproverEmailId)
                    {
                        CCAddressList = new StringBuilder();
                        CCAddressList.Append(sortedList[iCount].EmailAddress);
                        tmpsbMailBody = new StringBuilder();
                        GetUserApprovalMailBody(sortedList[iCount], ref tmpsbMailBody, i);
                        isSendMail = true;
                    }
                    else if (iCount < sortedList.Count - 1 && sortedList[iCount].ApproverEmailId == sortedList[iCount + 1].ApproverEmailId)
                    {
                        CCAddressList.Append(sortedList[iCount].EmailAddress);
                        CCAddressList.Append(";");
                        GetUserApprovalMailBody(sortedList[iCount], ref tmpsbMailBody, i);
                        isSendMail = false;
                        i++;
                    }
                    else
                    {
                        if (i == 1)
                        {
                            tmpsbMailBody = new StringBuilder();
                            CCAddressList = new StringBuilder();

                        }
                        CCAddressList.Append(sortedList[iCount].EmailAddress);
                        GetUserApprovalMailBody(sortedList[iCount], ref tmpsbMailBody, i);
                        isSendMail = true;

                    }
                    if (isSendMail)
                    {
                        Logger.HandleException("Sending Notification to Approver : " + sortedList[iCount].ApproverEmailId);
                        SendUserMail(tmpsbMailBody, sortedList[iCount].ApproverEmailId, tableHeader, CCAddressList.ToString());
                        isSendMail = false;
                        tmpsbMailBody = new StringBuilder();
                        CCAddressList = new StringBuilder();
                        i = 1;
                    }
                }
            }
        }

        private static string GetTableHeader()
        {
            string tableHeader = ("<table style='width:80%'  cellspacing='0' cellpadding='0'><tr  style='" + headerStyle + "'>" +
            "<td style='" + tdHeaderStyleLeft + "'>S.N.</td>" +
            "<td style='" + tdHeaderStyleOther + "'>User Name</td>" +
            "<td style='" + tdHeaderStyleOther + "'>Email</td>" +
            "<td style='" + tdHeaderStyleOther + "'>BU</td>" +
            "<td style='" + tdHeaderStyleOther + "'>Submitted Date</td>" +
            "<td style='" + tdHeaderStyleOther + "'>No. of days Pending</td>" +
            "</tr>");
            return tableHeader;
        }
        /// <summary>
        /// send mail for pending users
        /// </summary>
        /// <param name="tmpsbMailBody"></param>
        /// <param name="approver"></param>
        /// <param name="tableheader"></param>
        /// <param name="CCAddress"></param>
        /// <returns></returns>
        private static StringBuilder SendUserMail(StringBuilder tmpsbMailBody, string approver, string tableheader, string CCAddress)
        {
            StringBuilder sbEmailBody = new StringBuilder();
            //Remove semi-comma at the end
            if (CCAddress.IndexOf(';') == CCAddress.Length - 1)
                CCAddress = CCAddress.Remove(CCAddress.Length - 1, 1);
            sbEmailBody.Append("<HTML><BODY style='" + mailBodyStyle + "'>");
            sbEmailBody.Append("<br/> Below users are pending for your approval, please take appropriate action. (<a href='http://expense.logistics.corp/UserReport.aspx'>Approve/Reject</a>). <br/><br/>");
            sbEmailBody.Append("Email should go " + approver + "<br/><br/>"); // testing
            sbEmailBody.Append("CC Email should go " + CCAddress + "<br/><br/>"); // testing
            sbEmailBody.Append(tableheader + "" + tmpsbMailBody.ToString() + "</table>");
            sbEmailBody.Append("<br/></body></html>");
            Ceva.Net.MailHelper mailH = new MailHelper("CEVAExpense@cevalogistics.com", "aneeta.kanaujia@cevalogistics.com", "Reminder : CEVA Expense - New User Request", sbEmailBody.ToString(), null, null, true);
            mailH.SendMailMessage();
            
            return sbEmailBody;
        }

        public void GetUserApprovalMailBody(UserInfo objU, ref StringBuilder sb, int count)
        {
            string _rowStyle = string.Format(rowStyle, "#ffffff");
            if (count % 2 ==0)
                _rowStyle = string.Format(rowStyle, "#E1E1E1");

            sb.Append("<tr style='" + _rowStyle + "'>" +
            "<td  style='" + tdbodyStyleLeft + "'>" + count.ToString() + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + objU.FirstName + " " + objU.LastName + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + objU.EmailAddress + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + objU.BusinessUnitCd + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + objU.AddedDate.ToString() + "</td>" +
            "<td style='" + tdbodyStyleOther + "'>" + objU.NoOfPendingDays.ToString() + "</td></tr>");
        }

        #endregion

        #region Expense Details

        public void MailReminderExpense()
        {
            Logger.HandleException("Starting Notification Process");
            try
            {
                DataSet ds = GetExpenseReminderData();
                // string mailBody = "";
                string approverMailId = "";
                List<ExpenseDetail> lstExpense = new List<ExpenseDetail>();
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        // UserInfo objUser = new UserInfo();
                        // objUser = GetUserData(dr["UserId"].ToString());
                        //  DataSet dsExpanse = GetActionDetails(dr["NextActionId"].ToString(), "");
                        //  if (dsExpanse != null && dsExpanse.Tables.Count > 0 && dsExpanse.Tables[0].Rows.Count > 0)
                        //  {
                        ExpenseDetail expDtl = new ExpenseDetail();
                        expDtl.ExpenseRefNo = Convert.ToInt32(dr["ExpRequestId"]);
                        expDtl.Requester = Convert.ToString(dr["Requestor"]);
                        expDtl.ExpenseDate = Convert.ToDateTime(dr["AddedDate"]);
                        expDtl.NoOfPendingDays = Convert.ToInt32(dr["PendingDays"].ToString());
                        expDtl.ApproverMailId = Convert.ToString(dr["ApproverEmail"]);
                        expDtl.UserEmailId = Convert.ToString(dr["UserEmail"]);
                        lstExpense.Add(expDtl);
                        //Logger.HandleException("Processing for User ID " + dr["UserId"].ToString() + " and Action is " + dr["NextActionId"].ToString() + " and action values " + dr["ActionValue"].ToString());
                        //mailBody = GetExpenseMailBody(dsExpanse, objUser, GetActionDetails(dr["ExpActionId"].ToString(), "").Tables[0].Rows[0]["ApproverEmail"].ToString(), ref approverMailId);
                        Logger.HandleException("Sending Notification to Approver : " + approverMailId);
                        // Ceva.Net.MailHelper mailH = new MailHelper("CEVAExpense@cevalogistics.com", "aneeta.kanaujia@cevalogistics.com", "Reminder : CEVA Expense - Expense Request - (CE-" + dr["ExpRequestId"].ToString() + ")", mailBody, null, null, true);
                        // mailH.SendMailMessage();
                        // }
                    }
                    ProcessExpenseMail(lstExpense);
                }
                else
                {
                    Logger.HandleException("No Data found for notification");
                }
            }
            catch (Exception ex)
            {
                Logger.HandleException(ex);
            }

        }

        private void ProcessExpenseMail(List<ExpenseDetail> lstExpense)
        {
            bool isSendMail = false;
            StringBuilder tmpsbMailBody = new StringBuilder();
            StringBuilder CCAdddressList = new StringBuilder();
            string tableHeader = GetExpenseTableHeader();
            int i = 1;
            List<ExpenseDetail> sortedList = lstExpense.OrderBy(o => o.ApproverMailId).ToList();
            if (sortedList.Count == 1)
            {
                CCAdddressList.Append(sortedList[0].UserEmailId);
                GetExpenseApprovalMailBody(sortedList[0], ref tmpsbMailBody, i);
                SendExpenseMail(tmpsbMailBody, sortedList[0].ApproverMailId, tableHeader, CCAdddressList.ToString());
            }
            else
            {
                for (int iCount = 0; iCount < sortedList.Count; iCount++)
                {
                    if (iCount == 0 && sortedList[iCount].ApproverMailId != sortedList[iCount + 1].ApproverMailId)
                    {
                        tmpsbMailBody = new StringBuilder();
                        CCAdddressList = new StringBuilder();
                        CCAdddressList.Append(sortedList[iCount].UserEmailId);
                        GetExpenseApprovalMailBody(sortedList[iCount], ref tmpsbMailBody, i);
                        isSendMail = true;
                    }
                    else if (iCount < sortedList.Count - 1 && sortedList[iCount].ApproverMailId == sortedList[iCount + 1].ApproverMailId)
                    {
                        CCAdddressList.Append(sortedList[iCount].UserEmailId);
                        CCAdddressList.Append(";");
                        GetExpenseApprovalMailBody(sortedList[iCount], ref tmpsbMailBody, i);
                        isSendMail = false;
                        i++;
                    }
                    else
                    {
                        if (i == 1)
                        {
                            tmpsbMailBody = new StringBuilder();
                            CCAdddressList = new StringBuilder();
                        }

                        CCAdddressList.Append(sortedList[iCount].UserEmailId);
                        GetExpenseApprovalMailBody(sortedList[iCount], ref tmpsbMailBody, i);
                        isSendMail = true;

                    }
                    if (isSendMail)
                    {
                        Logger.HandleException("Sending Notification to Approver : " + sortedList[iCount].ApproverMailId);
                        SendExpenseMail(tmpsbMailBody, sortedList[iCount].ApproverMailId, tableHeader, CCAdddressList.ToString());
                        isSendMail = false;
                        tmpsbMailBody = new StringBuilder();
                        CCAdddressList = new StringBuilder();
                        i = 1;
                    }
                }
            }
        }

        /// <summary>
        /// send mail for pending expenses
        /// </summary>
        /// <param name="tmpsbMailBody"></param>
        /// <param name="approver"></param>
        /// <param name="tableHeader"></param>
        /// <param name="CCAddress"></param>
        private void SendExpenseMail(StringBuilder tmpsbMailBody, string approver, string tableHeader, string CCAddress)
        {
            StringBuilder sbEmailBody = new StringBuilder();
            //Remove semi-comma at the end
            if (CCAddress.IndexOf(';') == CCAddress.Length - 1)
                CCAddress = CCAddress.Remove(CCAddress.Length - 1, 1);

            sbEmailBody.Append("<HTML><BODY style='" + mailBodyStyle + "'>");
            sbEmailBody.Append("<br/> Below expenses are pending for your approval, please take appropriate action. (<a href='http://expense.logistics.corp/ExpenseApproval.aspx'>Approve/Reject</a>). <br/><br/>");
            sbEmailBody.Append("Email should go " + approver + "<br/><br/>"); // testing
            sbEmailBody.Append("CC Email should go " + CCAddress + "<br/><br/>"); // testing
            sbEmailBody.Append(tableHeader + "" + tmpsbMailBody.ToString() + "</table>");
            sbEmailBody.Append("<br/></body></html>");
            Ceva.Net.MailHelper mailH = new MailHelper("CEVAExpense@cevalogistics.com", "aneeta.kanaujia@cevalogistics.com", "Reminder : CEVA Expense -Expense Request", sbEmailBody.ToString(), null, null, true);
            mailH.SendMailMessage();
        }

        private static string GetExpenseTableHeader()
        {
            string tableHeader = ("<table style='width:70%'   cellspacing='0' cellpadding='0'><tr style='" + headerStyle + "'>" +
            "<td style='" + tdHeaderStyleLeft + "'>S.N.</td>" +
            "<td style='" + tdHeaderStyleOther + "'>Exp. Ref.#</td>" +
            "<td style='" + tdHeaderStyleOther + "'>Requester</td>" +
            "<td style='" + tdHeaderStyleOther + "'>Expense Date</td>" +
            "<td style='" + tdHeaderStyleOther + "'>No. of days Pending</td>" +
            "</tr>");
            return tableHeader;
        }

        private void GetExpenseApprovalMailBody(ExpenseDetail expenseDetail, ref StringBuilder sb, int count)
        {
            string _rowStyle = string.Format(rowStyle, "#ffffff");
            if (count % 2 == 0)
                _rowStyle = string.Format(rowStyle, "#E1E1E1");

            sb.Append("<tr style='" + _rowStyle + "'>" +
            "<td  style='" + tdbodyStyleLeft + "'>" + count.ToString() + "</td>" +
            "<td   style='" + tdbodyStyleOther + "'>" + expenseDetail.ExpenseRefNo.ToString() + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + expenseDetail.Requester + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + expenseDetail.ExpenseDate.ToString() + "</td>" +
            "<td  style='" + tdbodyStyleOther + "'>" + expenseDetail.NoOfPendingDays.ToString() + "</td>" +
            "</tr>");

        }

        #endregion

        #region Un-Used Methods
        private string BuildSubject(string expanseId)
        {
            return "CEVA Expense - Expense Request - (CE-" + expanseId + ")";
        }
        private string BuildLink(string guid, string status)
        {
            return "<a href='http://expense.logistics.corp/ExpenseAction.aspx?actionId=" + guid + "&action=" + status + "'>";
        }
        public static Double GetTotalExpAmt(List<ExpType> oExpTypes)
        {
            Double dblTotal = 0;
            foreach (ExpType et in oExpTypes)
            {
                dblTotal += et.ExpAmount;
            }
            return dblTotal;
        }
        public string GetApproverMailBody(UserInfo oUserInfo, String strActionId, String strInto, String strApproverStatus, string strNextEmailAddress, ref string approverMail)
        {
            StringBuilder sbEmailBody = new StringBuilder();
            string strApproverEmail = "";
            sbEmailBody.Append("<HTML><BODY style='font: normal 11pt Calibri, Arial, Helvetica, sans-serif;'>");
            sbEmailBody.Append("<br/>" + strInto + "<br/><br/>");
            sbEmailBody.Append("<span style='font: bold 13pt Calibri, Arial, Helvetica, sans-serif;'>User Details</span><br/>");
            sbEmailBody.Append("Employee Id: " + oUserInfo.EmployeeId + "<br/>");
            sbEmailBody.Append("Name: " + oUserInfo.LastName + ", " + oUserInfo.FirstName + "<br/>");
            sbEmailBody.Append("Title: " + oUserInfo.Title + "<br/>");
            sbEmailBody.Append("Email: " + oUserInfo.EmailAddress + "<br/>");
            sbEmailBody.Append("Office Phone: " + oUserInfo.OfficePhone + "<br/>");
            sbEmailBody.Append("Mobile Phone: " + oUserInfo.MobilePhone + "<br/>");
            sbEmailBody.Append("Company: " + GetRefData("COMP").Tables[0].Select("ValueCd='" + oUserInfo.CompanyCd + "'")[0]["ValueDesc"] + "<br/>");
            sbEmailBody.Append("Location: " + oUserInfo.LocationCd + "<br/>");
            sbEmailBody.Append("Business Unit: " + oUserInfo.BusinessUnitCd + "<br/>");
            sbEmailBody.Append("Department: " + oUserInfo.DeptCd + "<br/><br/>");
            sbEmailBody.Append("<span style='font: bold 13pt Calibri, Arial, Helvetica, sans-serif;'>Expense Details</span><br/>");
            if (oUserInfo.ExpTypesStage.Count > 0)
                sbEmailBody.Append("Current:<br/>");

            sbEmailBody.Append("Phone Type: " + oUserInfo.ExpTypes.Find(m => m.ExpTypeCode == "PT").ExpTypeName + "<br/>");
            sbEmailBody.Append("Data Type: " + oUserInfo.ExpTypes.Find(m => m.ExpTypeCode == "DCT").ExpTypeName + "<br/>");

            if (oUserInfo.ExpTypesStage.Count > 0)
            {
                sbEmailBody.Append("Requesting:<br/>");
                if (oUserInfo.ExpTypesStage.Find(m => m.ExpTypeCode == "PT") != null)
                    sbEmailBody.Append("Phone Type: " + oUserInfo.ExpTypesStage.Find(m => m.ExpTypeCode == "PT").ExpTypeName + "<br/>");

                if (oUserInfo.ExpTypesStage.Find(m => m.ExpTypeCode == "DCT") != null)
                    sbEmailBody.Append("Data Type: " + oUserInfo.ExpTypesStage.Find(m => m.ExpTypeCode == "DCT").ExpTypeName + "<br/>");

            }
            sbEmailBody.Append("<br/>");

            if (oUserInfo.ExpTypesStage.Count > 0)
                sbEmailBody.Append("This user is requesting a monthly allowance of <b>$" + GetTotalExpAmt(oUserInfo.ExpTypesStage).ToString() + "</b><br/><br/>");
            else
                sbEmailBody.Append("This user is requesting a monthly allowance of <b>$" + GetTotalExpAmt(oUserInfo.ExpTypes).ToString() + "</b><br/><br/>");

            strApproverEmail = GetApproverMailId(oUserInfo, strApproverStatus, strNextEmailAddress, strActionId);

            if (strActionId != "")
            {
                if ((strApproverStatus == "PAA" || strApproverStatus == "A") && strNextEmailAddress == "")
                {
                    //'BU Owner approver Check to see if finance is auto setup from user insert proc.  
                    //'If so the approval will approve the user registration.  If not it needs to go to finance for approval
                    if (oUserInfo.VendorNo == "" && strApproverStatus != "A")
                    {
                        sbEmailBody.Append("You can " + BuildLink(strActionId, strApproverStatus) + "Approve</a> or" + BuildLink(strActionId, "R") + "Reject</a> this request directly from this email.");

                    }
                    else
                    {
                        sbEmailBody.Append("You can " + BuildLink(strActionId, "A") + "Approve</a> or " + BuildLink(strActionId, "R") + "Reject</a> this request directly from this email.");
                    }

                }
                else
                {

                    //accounting approval
                    strApproverEmail = GetRefData("ACCTEM").Tables[0].Select("ValueCd='" + oUserInfo.BUTypeCd + "'")[0]["ValueDesc"].ToString();
                    sbEmailBody.Append("Please verify this user is setup in the financial system to submit expense requests.  Then verify their information in the CEVA Expense system and approve them.<br/>You can " + BuildLink(strActionId, strApproverStatus) + "View</a> this request directly from this email.");
                }

            }
            sbEmailBody.Append("<br/>");
            approverMail = strApproverEmail;
            // 'Override Email addr for testing

            sbEmailBody.Append("</BODY></HTML>");

            return sbEmailBody.ToString();
        }


        public string GetExpenseMailBody(DataSet dsAction, UserInfo oUserInfo, string strAppEmail, ref string approverMailId)
        {
            StringBuilder sbEmailBody = new StringBuilder();
            String strApproverEmail = strAppEmail;
            String strUserMonthlyAllowance = GetTotalExpAmt(oUserInfo.ExpTypes).ToString();

            sbEmailBody.Append("<HTML><BODY style='font: normal 11pt Calibri, Arial, Helvetica, sans-serif;'>");
            //With dsAction.Tables(0).Rows(0)
            sbEmailBody.Append("A Cellular Expense has been submitted by " + dsAction.Tables[0].Rows[0]["SubmittedByUser"].ToString());
            if (dsAction.Tables[0].Rows[0]["ExpUser"].ToString() != "" && dsAction.Tables[0].Rows[0]["SubmittedByUser"] != dsAction.Tables[0].Rows[0]["ExpUser"])
                sbEmailBody.Append(" for " + dsAction.Tables[0].Rows[0]["ExpUser"]);

            sbEmailBody.Append(" in the amount of <b>$" + Math.Round(Convert.ToDecimal(dsAction.Tables[0].Rows[0]["ExpAmount"].ToString() == "" ? "0" : dsAction.Tables[0].Rows[0]["ExpAmount"].ToString()), 2).ToString() + "</b>.  ");
            switch (dsAction.Tables[0].Rows[0]["ApprovalType"].ToString())
            {
                case "1":
                    sbEmailBody.Append("This request has exceeded the users monthly limit of <b>$" + strUserMonthlyAllowance + "</b>");
                    break;
                case "2":
                    sbEmailBody.Append("Please review this entry since it has exceeded the users monthly limit (<b>$" + strUserMonthlyAllowance + "</b>) and the user has already submitted a request this calendar month.");
                    break;
                case "3":
                    sbEmailBody.Append("Please review this entry since the user has already submitted an expense this calendar month.");
                    break;
                default:
                    break;
            }

            sbEmailBody.Append("<br/><br/>");
            if (dsAction.Tables[0].Rows[0]["Comments"] != null)
            {
                sbEmailBody.Append("The user has left the following comment:<br/>");
                sbEmailBody.Append(dsAction.Tables[0].Rows[0]["Comments"].ToString());
            }
            sbEmailBody.Append("<br/><br/>");
            sbEmailBody.Append("You can " + BuildLink(dsAction.Tables[0].Rows[0]["ExpActionId"].ToString(), dsAction.Tables[0].Rows[0]["ActionValue"].ToString()) + " Approve</A>, " + BuildLink(dsAction.Tables[0].Rows[0]["ExpActionId"].ToString(), "R") + " Reject</A> or " + BuildLink(dsAction.Tables[0].Rows[0]["ExpActionId"].ToString(), "V") + " View</A> this expense");
            sbEmailBody.Append("<br/>");


            sbEmailBody.Append("</BODY></HTML>");

            return sbEmailBody.ToString();
        }

        #endregion

        #region Private Methods
        private DataSet GetUserReminderData()
        {
            try
            {
                Logger.HandleException("Getting Data for Notification");
                DataSet ds = Ceva.Data.SqlHelper.ExecuteDataset(new SqlConnection(constr), CommandType.StoredProcedure, "GetPendingApprovalRecordsUser_PR");
                if (ds != null)
                    Logger.WriteFile("Total number of rows found " + ds.Tables[0].Rows.Count.ToString());
                return ds;
            }
            catch (Exception ex)
            {
                Logger.HandleException(ex);
                return null;
            }

        }

        private DataSet GetExpenseReminderData()
        {
            try
            {
                Logger.HandleException("Getting Data for Notification");
                DataSet ds = Ceva.Data.SqlHelper.ExecuteDataset(new SqlConnection(constr), CommandType.StoredProcedure, "GetPendingApprovalRecordsExpense_PR");
                if (ds != null)
                    Logger.WriteFile("Total number of rows found " + ds.Tables[0].Rows.Count.ToString());
                return ds;
            }
            catch (Exception ex)
            {
                Logger.HandleException(ex);
                return null;
            }

        }

        private DataSet GetActionDetails(string strExpActionId, string strAction)
        {
            List<SqlParameter> param = null;
            param = new List<SqlParameter>();
            SqlParameter pp = null;
            pp = new SqlParameter();
            pp.ParameterName = "@ExpActionId";
            pp.Value = new Guid(strExpActionId);
            pp.DbType = DbType.Guid;
            param.Add(pp);
            pp = new SqlParameter();
            pp.ParameterName = "@ExpActionCd";
            pp.Value = strAction;
            param.Add(pp);
            return SqlHelper.ExecuteDataset(constr, "ExpAction_Get_PR", param.ToArray());
        }

        private string GetBUInfo(string butypecd, string buid, string deptCd, string type)
        {
            string sql = @" SELECT aubu.EmailAddress as BUOwnerEmail,auSVP.EmailAddress as SVPCostOwnerEmail
	                        FROM BUOwner bu
	                        JOIN AppUser aubu on aubu.UserId = bu.BUOwnerUserId
	                        JOIN AppUser auSVP on auSVP.UserId = bu.SVPCostOwnerUserId
	                        WHERE bu.BUTypeCd='" + butypecd + "' AND bu.DeptCd='" + deptCd + "' AND bu.BUCd='" + buid + "' ";
            DataSet ds = Ceva.Data.SqlHelper.ExecuteDataset(constr, CommandType.Text, sql);
            if (type == "A")
                return ds.Tables[0].Rows[0][0].ToString();
            else
                return ds.Tables[0].Rows[0][1].ToString();
        }

        private string GetApproverMailId(UserInfo oUserInfo, string strApproverStatus, string strNextEmailAddress, string strActionId)
        {
            string strApproverEmail = GetBUInfo(oUserInfo.BUTypeCd, oUserInfo.BusinessUnitCd, oUserInfo.DeptCd, "A");
            //If it is their own go to next level
            if (strApproverEmail == oUserInfo.EmailAddress)
            {
                strApproverEmail = GetBUInfo(oUserInfo.BUTypeCd, oUserInfo.BusinessUnitCd, oUserInfo.DeptCd, "P");
                if (strApproverEmail == oUserInfo.EmailAddress)
                    strApproverEmail = GetUserData(GetRefData("PRESID").Tables[0].Rows[0]["ValueCd"].ToString()).EmailAddress;

            }
            if (strApproverStatus == "PAA")
            {
                strApproverEmail = GetRefData("ACCTEM").Tables[0].Select("ValueCd='" + oUserInfo.BUTypeCd + "'")[0]["ValueDesc"].ToString();
            }
            return strApproverEmail;
        }

        private DataSet GetRefData(string reName)
        {
            return SqlHelper.ExecuteDataset(constr, "AppValues_Get_PR", reName);
        }

        private UserInfo GetUserData(String strNetworkLogin)
        {

            long iUserId = -1;
            long.TryParse(strNetworkLogin, out iUserId);
            Logger.HandleException("Getting record for user " + strNetworkLogin);
            DataSet dsUser = SqlHelper.ExecuteDataset(constr, "AppUser_Get_PR", strNetworkLogin, iUserId);
            if (dsUser.Tables[0].Rows.Count == 0)
                return null;
            Logger.HandleException("Data fetched successfully. Processing records");
            UserRole objUserRole = null;
            UserInfo oUserInfo = new UserInfo();
            for (int i = 0; i < dsUser.Tables[0].Rows.Count; i++)
            {

                if (i == 0)
                {
                    oUserInfo.UserId = Convert.ToInt64(dsUser.Tables[0].Rows[0]["UserId"].ToString());
                    Logger.HandleException("User id found is " + oUserInfo.UserId.ToString());
                    oUserInfo.EmployeeId = dsUser.Tables[0].Rows[0]["EmployeeId"].ToString();
                    oUserInfo.VendorNo = dsUser.Tables[0].Rows[0]["VendorNo"].ToString();
                    oUserInfo.NetworkLogin = dsUser.Tables[0].Rows[0]["NetworkLogin"].ToString();
                    oUserInfo.FirstName = dsUser.Tables[0].Rows[0]["FirstName"].ToString();
                    oUserInfo.LastName = dsUser.Tables[0].Rows[0]["LastName"].ToString();
                    oUserInfo.Title = dsUser.Tables[0].Rows[0]["Title"].ToString();
                    oUserInfo.EmailAddress = dsUser.Tables[0].Rows[0]["EmailAddress"].ToString();
                    oUserInfo.OfficePhone = dsUser.Tables[0].Rows[0]["OfficePhone"].ToString();
                    oUserInfo.MobilePhone = dsUser.Tables[0].Rows[0]["MobilePhone"].ToString();
                    oUserInfo.BusinessUnitCd = dsUser.Tables[0].Rows[0]["BusinessUnitCd"].ToString();
                    oUserInfo.BUTypeCd = dsUser.Tables[0].Rows[0]["BUTypeCd"].ToString();
                    oUserInfo.DeptCd = dsUser.Tables[0].Rows[0]["DeptCd"].ToString();
                    oUserInfo.CompanyCd = dsUser.Tables[0].Rows[0]["CompanyCd"].ToString();
                    oUserInfo.ApproverUserId = Convert.ToInt64(dsUser.Tables[0].Rows[0]["ApproverUserId"].ToString());
                    oUserInfo.LocationCd = dsUser.Tables[0].Rows[0]["LocationCd"].ToString();
                    oUserInfo.VendorSite = dsUser.Tables[0].Rows[0]["VendorSite"].ToString();
                    oUserInfo.StatusCd = dsUser.Tables[0].Rows[0]["StatusCd"].ToString();
                    oUserInfo.AddUpdUserId = dsUser.Tables[0].Rows[0]["AddedByUser"].ToString();
                    oUserInfo.SubscriberNo = dsUser.Tables[0].Rows[0]["SubscriberNo"].ToString();
                    oUserInfo.ILDeviceCount = Convert.ToInt32(dsUser.Tables[0].Rows[0]["ILDeviceCount"].ToString());
                    oUserInfo.ExpTypes = new List<ExpType>();
                    oUserInfo.ExpTypesStage = new List<ExpType>();
                    oUserInfo.UserRoles = new List<UserRole>();
                    oUserInfo.UserDeletegates = new List<UserDelegate>();
                }
                objUserRole = new UserRole();
                objUserRole.AppRoleCode = dsUser.Tables[0].Rows[i]["AppRoleCd"].ToString();
                objUserRole.AppRoleId = dsUser.Tables[0].Rows[i]["AppRoleId"].ToString();
                objUserRole.AppRoleDesc = dsUser.Tables[0].Rows[i]["AppRoleDesc"].ToString();
                oUserInfo.UserRoles.Add(objUserRole);
            }
            ExpType exp = null;
            double amt;
            for (int j = 0; j < dsUser.Tables[1].Rows.Count; j++)
            {
                exp = new ExpType();
                exp.ExpGrpCode = dsUser.Tables[1].Rows[j]["ExpGrpCd"].ToString();
                exp.ExpTypeCode = dsUser.Tables[1].Rows[j]["ExpTypeCd"].ToString();
                exp.ExpTypeCodeVal = dsUser.Tables[1].Rows[j]["ExpTypeCdVal"].ToString();
                Double.TryParse(dsUser.Tables[1].Rows[j]["AmountLimit"].ToString(), out amt);
                exp.ExpAmount = amt;
                //  exp.ExpAmount = Convert.ToInt64(=="0.00"?"0":dsUser.Tables[1].Rows[j]["AmountLimit"].ToString());
                exp.ExpTypeName = dsUser.Tables[1].Rows[j]["ExpenseTypeName"].ToString();
                oUserInfo.ExpTypes.Add(exp);
            }

            ExpType expstage = null;
            for (int j = 0; j < dsUser.Tables[2].Rows.Count; j++)
            {
                expstage = new ExpType();
                expstage.ExpGrpCode = dsUser.Tables[2].Rows[j]["ExpGrpCd"].ToString();
                expstage.ExpTypeCode = dsUser.Tables[2].Rows[j]["ExpTypeCd"].ToString();
                expstage.ExpTypeCodeVal = dsUser.Tables[2].Rows[j]["ExpTypeCdVal"].ToString();
                expstage.ExpAmount = Convert.ToInt64(dsUser.Tables[2].Rows[j]["AmountLimit"] == null ? 0 : dsUser.Tables[2].Rows[j]["AmountLimit"]);
                expstage.ExpTypeName = dsUser.Tables[2].Rows[j]["ExpenseTypeName"].ToString();
                oUserInfo.ExpTypesStage.Add(expstage);
            }

            UserDelegate del = null;
            for (int k = 0; k < dsUser.Tables[3].Rows.Count; k++)
            {
                del = new UserDelegate();
                del.UserId = Convert.ToInt64(dsUser.Tables[3].Rows[k]["DelegateUserId"].ToString());
                del.EmployeeId = dsUser.Tables[3].Rows[k]["EmployeeId"].ToString();
                del.VendorNo = dsUser.Tables[3].Rows[k]["VendorNo"].ToString();
                del.NetworkLogin = dsUser.Tables[3].Rows[k]["NetworkLogin"].ToString();
                del.FirstName = dsUser.Tables[3].Rows[k]["FirstName"].ToString();
                del.LastName = dsUser.Tables[3].Rows[k]["LastName"].ToString();
                del.Title = dsUser.Tables[3].Rows[k]["Title"].ToString();
                del.EmailAddress = dsUser.Tables[3].Rows[k]["EmailAddress"].ToString();
                del.OfficePhone = dsUser.Tables[3].Rows[k]["OfficePhone"].ToString();
                del.MobilePhone = dsUser.Tables[3].Rows[k]["MobilePhone"].ToString();
                del.BusinessUnitCd = dsUser.Tables[3].Rows[k]["BusinessUnitCd"].ToString();
                del.BUTypeCd = dsUser.Tables[3].Rows[k]["BUTypeCd"].ToString();
                del.DeptCd = dsUser.Tables[3].Rows[k]["DeptCd"].ToString();
                del.CompanyCd = dsUser.Tables[3].Rows[k]["CompanyCd"].ToString();
                del.ApproverUserId = Convert.ToInt64(dsUser.Tables[3].Rows[k]["ApproverUserId"].ToString());
                del.LocationCd = dsUser.Tables[3].Rows[k]["LocationCd"].ToString();
                del.VendorSite = dsUser.Tables[3].Rows[k]["VendorSite"].ToString();
                del.StatusCd = dsUser.Tables[3].Rows[k]["StatusCd"].ToString();
                del.AddUpdUserId = dsUser.Tables[3].Rows[k]["AddedByUser"].ToString();
                // del.SubscriberNo = dsUser.Tables[3].Rows[k]["SubscriberNo"].ToString();
                del.DelegateTypeCd = dsUser.Tables[3].Rows[k]["DelegateTypeCd"].ToString();
                oUserInfo.UserDeletegates.Add(del);
            }


            return oUserInfo;
        }
        #endregion
    }


    #region Supporting Classes

    public class ExpenseDetail
    {
        public int ExpenseRefNo { get; set; }
        public string Requester { get; set; }
        public DateTime ExpenseDate { get; set; }
        public int NoOfPendingDays { get; set; }
        public string ApproverMailId { get; set; }
        public string UserEmailId { get; set; }
    }
    public class UserDelegate : UserInfo
    {
        public string DelegateTypeCd { get; set; }
    }
    public class UserInfo
    {
        #region "Properties"
        public DateTime AddedDate { get; set; }
        public int NoOfPendingDays { get; set; }
        public string ApproverEmailId { get; set; }
        public long UserId { get; set; }
        public string EmployeeId { get; set; }
        public string VendorNo { get; set; }
        public string NetworkLogin { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Title { get; set; }
        public string EmailAddress { get; set; }
        public string OfficePhone { get; set; }
        public string MobilePhone { get; set; }
        public string SubscriberNo { get; set; }
        public string BusinessUnitCd { get; set; }
        public string BUTypeCd { get; set; }
        public string DeptCd { get; set; }
        public string CompanyCd { get; set; }
        public string LocationCd { get; set; }
        public string VendorSite { get; set; }
        public long ApproverUserId { get; set; }
        public int ILDeviceCount { get; set; }
        public string StatusCd { get; set; }
        public string AddUpdUserId { get; set; }
        public List<ExpType> ExpTypes { get; set; }
        public List<ExpType> ExpTypesStage { get; set; }
        public List<UserRole> UserRoles { get; set; }
        public List<UserDelegate> UserDeletegates { get; set; }

        #endregion
    }
    public class ExpType
    {
        public string ExpGrpCode { get; set; }
        public string ExpTypeCodeVal { get; set; }
        public string ExpTypeCode { get; set; }
        public double ExpAmount { get; set; }
        public string ExpTypeName { get; set; }

    }
    public class UserRole
    {
        #region "Properties"
        public string AppRoleId { get; set; }
        public string AppRoleCode { get; set; }
        public string AppRoleDesc { get; set; }
        #endregion
    }
    public class Logger
    {
        public static String strErrFileName { get; set; }

        static Logger()
        {
            strErrFileName = ConfigurationSettings.AppSettings["ErrorLogPath"].ToString();

        }

        public static void WriteFile(String strText)
        {
            try
            {
                using (StreamWriter streamWriter = new StreamWriter(new FileStream(strErrFileName, System.IO.FileMode.Append)))
                {
                    streamWriter.WriteLine(strText);
                    streamWriter.Flush();
                    streamWriter.Close();
                }
            }
            catch (Exception ex) { throw ex; }
        }
        public static void HandleException(Exception ex)
        {
            WriteFile("Exception:" + DateTime.Now.ToString());
            WriteFile("********************************************\n\n");
            WriteFile(ex.Message);
            WriteFile("");
            WriteFile(ex.StackTrace);
            WriteFile("********************************************\n\n");
            WriteFile("");
        }

        public static void HandleException(string message)
        {
            WriteFile("Message:" + DateTime.Now.ToString());
            WriteFile("********************************************\n\n");
            WriteFile(message);
            WriteFile("");
            WriteFile("********************************************\n\n");
            WriteFile("");
        }

    }

    #endregion

}
